package com.zhan.ielts.tools;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.zhan.ielts.domain.CompositionJjForecast;

public class CompositionJjForecastUtils {
	private static Logger log = LoggerFactory.getLogger(CompositionJjForecastUtils.class);
	private static Connection conn = getConnection();

	public static void main(String[] args) {
		log.info("-----start");

		String filepath = "/xiaozhan/received files/jiang qian/0330/雅思写作机经预测——0406.xlsx";
		importZhentiComposition(filepath);

		log.info("-----end");
	}

	public static void importZhentiComposition(String filePath) {
		try {
			XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(filePath));
			List<CompositionJjForecast> list = new ArrayList<CompositionJjForecast>();
			int count = 1;
			String lastExamDate = "";
			CompositionJjForecast compositionJjForecast = new CompositionJjForecast();
			JSONArray dataArray = new JSONArray();
			for (int k = 0; k < 1; k++) {

				XSSFSheet sheet = wb.getSheetAt(k);
				XSSFRow row = null;
				XSSFCell cell = null;

				for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
					// for (int i = 0; i < 100; i++) {
					row = sheet.getRow(i);
					if (row.getCell(0) == null) {
						break;
					}
					System.out.println("k->" + k + "--i->" + i);
					String cell0 = "";
					if (row.getCell((short) 0).getCellType() == XSSFCell.CELL_TYPE_STRING) {
						cell0 = String.valueOf(row.getCell((short) 0).getStringCellValue());
					} else {
						cell0 = String.valueOf(row.getCell((short) 0).getRawValue());
					}
					if (StringUtils.isEmpty(cell0) || cell0.equals("null")) {
						break;
					}
					System.out.println(cell0);
					String examDate = cell0.substring(0, 8);
					if(!examDate.equals(lastExamDate)){
						compositionJjForecast = new CompositionJjForecast();
						compositionJjForecast.setId(String.valueOf(count++));
						dataArray = new JSONArray();
					}
					
					String form = cell0.length() > 8 ? cell0.substring(8).trim() : "-";

//					CompositionJjForecast compositionJjForecast = new CompositionJjForecast();
//					compositionJjForecast.setId(String.valueOf(count++));
					String date = String.valueOf(row.getCell((short) 0).getRawValue());
					compositionJjForecast.setTitle(date.substring(0, 4) + "年" + date.substring(4, 6) + "月"
							+ date.substring(6, 8) + "日");
//					compositionJjForecast.setCompositionType(String.valueOf(row.getCell((short) 1)));

					// 获取1，2，3，4级重点
					JSONObject jsonObject = new JSONObject();
					JSONArray jsonArray = new JSONArray();
					JSONObject tmpJsonObject = new JSONObject();
					JSONArray tmpJsonArray = new JSONArray();
//					JSONArray dataArray = new JSONArray();
					
					String level1 = String.valueOf(row.getCell((short) 2));
					String level2 = String.valueOf(row.getCell((short) 3));
					String level3 = String.valueOf(row.getCell((short) 4));
					String level4 = String.valueOf(row.getCell((short) 5));

					String relatedId = String.valueOf(row.getCell((short) 6));

					tmpJsonObject.element("title", "一级重点");
					// tmpJsonArray.add(Arrays.asList(level1.split("/")));
					tmpJsonObject.element("content", Arrays.asList(level1.split("；")));
					jsonArray.add(tmpJsonObject);

					tmpJsonObject.element("title", "二级重点");
					tmpJsonArray.clear();
					// tmpJsonArray.add(Arrays.asList(level2.split("/")));
					tmpJsonObject.element("content", Arrays.asList(level2.split("；")));
					jsonArray.add(tmpJsonObject);

					tmpJsonObject.element("title", "三级重点");
					tmpJsonArray.clear();
					// tmpJsonArray.add(Arrays.asList(level3.split("/")));
					tmpJsonObject.element("content", Arrays.asList(level3.split("；")));
					jsonArray.add(tmpJsonObject);

					tmpJsonObject.element("title", "四级重点");
					tmpJsonArray.clear();
					// tmpJsonArray.add(Arrays.asList(level4.split("/")));
					tmpJsonObject.element("content", Arrays.asList(level4.split("；")));
					jsonArray.add(tmpJsonObject);
					tmpJsonArray.clear();

					jsonObject.element("important", jsonArray);
					jsonObject.element("compositionType",String.valueOf(row.getCell((short) 1)));
					
					
					if (StringUtils.isEmpty(relatedId) || relatedId.equals("null")) {
						jsonObject.element("id", new ArrayList());
					} else {
						// jsonObject.element("id",
						// Arrays.asList(relatedId.split("/")));
						List idList = Arrays.asList(relatedId.split("/"));
						List practiceList = new ArrayList();
						
						for (int m = 0; m < idList.size(); m++) {
							List tmpPracticeList = getZhentiComposition(idList.get(m).toString(),String.valueOf(row.getCell((short) 1)));
							if(tmpPracticeList == null || tmpPracticeList.size()==0){
								practiceList.add("");
							}else{
								practiceList.add(tmpPracticeList.get(0));
							}
						}
						jsonObject.element("id", practiceList);
					}
					
					dataArray.add(jsonObject);
//					compositionJjForecast.setData(jsonObject.toString().replace(" ", ""));
					compositionJjForecast.setData(dataArray.toString().replace(" ", ""));
					Thread.sleep(1);
					if(examDate.equals(lastExamDate)){
						list.add(compositionJjForecast);
					}
					lastExamDate = examDate;


				}
			}

			updateCompositionJjForecast(list);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public static void importZhentiComposition_ori(String filePath) {
		try {
			XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(filePath));
			List<CompositionJjForecast> list = new ArrayList<CompositionJjForecast>();
			int count = 1;
			for (int k = 0; k < 1; k++) {

				XSSFSheet sheet = wb.getSheetAt(k);
				XSSFRow row = null;
				XSSFCell cell = null;

				for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
					// for (int i = 0; i < 100; i++) {
					row = sheet.getRow(i);
					if (row.getCell(0) == null) {
						break;
					}
					System.out.println("k->" + k + "--i->" + i);
					String cell0 = "";
					if (row.getCell((short) 0).getCellType() == XSSFCell.CELL_TYPE_STRING) {
						cell0 = String.valueOf(row.getCell((short) 0).getStringCellValue());
					} else {
						cell0 = String.valueOf(row.getCell((short) 0).getRawValue());
					}
					if (StringUtils.isEmpty(cell0) || cell0.equals("null")) {
						break;
					}
					System.out.println(cell0);
					String examDate = cell0.substring(0, 8);
					String form = cell0.length() > 8 ? cell0.substring(8).trim() : "-";

					CompositionJjForecast compositionJjForecast = new CompositionJjForecast();
					compositionJjForecast.setId(String.valueOf(count++));
					String date = String.valueOf(row.getCell((short) 0).getRawValue());
					compositionJjForecast.setTitle(date.substring(0, 4) + "年" + date.substring(4, 6) + "月"
							+ date.substring(6, 8) + "日");
					compositionJjForecast.setCompositionType(String.valueOf(row.getCell((short) 1)));

					// 获取1，2，3，4级重点
					JSONObject jsonObject = new JSONObject();
					JSONArray jsonArray = new JSONArray();
					JSONObject tmpJsonObject = new JSONObject();
					JSONArray tmpJsonArray = new JSONArray();
					JSONArray dataArray = new JSONArray();
					
					String level1 = String.valueOf(row.getCell((short) 2));
					String level2 = String.valueOf(row.getCell((short) 3));
					String level3 = String.valueOf(row.getCell((short) 4));
					String level4 = String.valueOf(row.getCell((short) 5));

					String relatedId = String.valueOf(row.getCell((short) 6));

					tmpJsonObject.element("title", "一级重点");
					// tmpJsonArray.add(Arrays.asList(level1.split("/")));
					tmpJsonObject.element("content", Arrays.asList(level1.split("；")));
					jsonArray.add(tmpJsonObject);

					tmpJsonObject.element("title", "二级重点");
					tmpJsonArray.clear();
					// tmpJsonArray.add(Arrays.asList(level2.split("/")));
					tmpJsonObject.element("content", Arrays.asList(level2.split("；")));
					jsonArray.add(tmpJsonObject);

					tmpJsonObject.element("title", "三级重点");
					tmpJsonArray.clear();
					// tmpJsonArray.add(Arrays.asList(level3.split("/")));
					tmpJsonObject.element("content", Arrays.asList(level3.split("；")));
					jsonArray.add(tmpJsonObject);

					tmpJsonObject.element("title", "四级重点");
					tmpJsonArray.clear();
					// tmpJsonArray.add(Arrays.asList(level4.split("/")));
					tmpJsonObject.element("content", Arrays.asList(level4.split("；")));
					jsonArray.add(tmpJsonObject);
					tmpJsonArray.clear();

					jsonObject.element("important", jsonArray);
//					jsonObject.element("compositionType",String.valueOf(row.getCell((short) 1)));
//					dataArray.add(jsonObject);
					
					
					if (StringUtils.isEmpty(relatedId) || relatedId.equals("null")) {
						jsonObject.element("id", new ArrayList());
					} else {
						// jsonObject.element("id",
						// Arrays.asList(relatedId.split("/")));
						List idList = Arrays.asList(relatedId.split("/"));
						List practiceList = new ArrayList();
						
						for (int m = 0; m < idList.size(); m++) {
							List tmpPracticeList = getZhentiComposition(idList.get(m).toString(),String.valueOf(row.getCell((short) 1)));
							if(tmpPracticeList == null || tmpPracticeList.size()==0){
								practiceList.add("");
							}else{
								practiceList.add(tmpPracticeList.get(0));
							}
						}
						jsonObject.element("id", practiceList);
					}

					compositionJjForecast.setData(jsonObject.toString().replace(" ", ""));
					Thread.sleep(1);
					list.add(compositionJjForecast);

				}
			}

			updateCompositionJjForecast(list);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public static List<String> getZhentiComposition(String date, String compositionType) throws ClassNotFoundException,
			SQLException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		// Connection conn = getConnection();
		if (conn == null) {
			conn = getConnection();
		}
		conn.setAutoCommit(false);
		stmt = conn
				.prepareStatement("select id from t_zhenti_composition where concat(year,month,date,form) = ?  and composition_type = ? ");
		stmt.setString(1, date);
		stmt.setString(2, compositionType);
		rs = stmt.executeQuery();
		List<String> list = new ArrayList<String>();
		while (rs.next()) {
			list.add(rs.getString(1));
		}
		return list;
	}

	public static void updateCompositionJjForecast(List<CompositionJjForecast> list) throws ClassNotFoundException,
			SQLException, InterruptedException {
		if (list == null || list.size() == 0)
			return;

		// Connection conn = getConnection();
		if (conn == null) {
			conn = getConnection();
		}
		conn.setAutoCommit(false);
		PreparedStatement stmt = conn
				.prepareStatement("replace into t_composition_jj_forecast (id, title, composition_type,data, update_time)VALUES (?, ?, ?, ?, ?)");

		for (int n = 0; n < list.size(); n++) {
			// System.out.println(list.get(n));
			CompositionJjForecast compositionJjForecast = list.get(n);
			stmt.setString(1, compositionJjForecast.getId());
			stmt.setString(2, compositionJjForecast.getTitle().trim());
			stmt.setString(3, compositionJjForecast.getCompositionType());
			stmt.setString(4, compositionJjForecast.getData());
			stmt.setLong(5, System.currentTimeMillis());
			Thread.sleep(1);
			stmt.addBatch();
		}

		stmt.executeBatch();
		conn.commit();
	}

	public static Connection getConnection() {
		String className = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://192.168.1.188:3306/ieltsdb?zeroDateTimeBehavior=convertToNull";
		String username = "ieltsdba";
		String userpass = "zaq1_XSW2";
		Connection con = null;
		try {
			Class.forName(className);
			con = DriverManager.getConnection(url, username, userpass);
		} catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return con;
	}

}
